// Created by Windward Studios - no copyright is claimed. This code can be used in
// any manner by anyone for any reason. There is no copyright of any kind on it. You may
// use it in commercial products. You may change it without sharing those changes.
// We ask that you keep the "created by Windward Studios" in a comment at the top.

using System;
using System.Collections.Specialized;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Text;
using net.windward.utils.ado.OleDb;
using NUnit.Framework;

// test all metadata including PK:FK for identically named tables under different schemas. Can use KailuaTest DB.

namespace net.windward.utils.ado
{
	[TestFixture]
	public class TestOleDbProvider
	{
		private string serverName;
		private string databaseName;
		private string username;
		private string password;
		private static readonly string OleDbProvider = "SQLNCLI";

		readonly DbProviderFactory providerFactory = DbProviderFactories.GetFactory(WrOleDbVendor.FactoryClass);

		static TestOleDbProvider()
		{
			WrOleDbVendor vendor = (WrOleDbVendor)WrProviderFactories.CreateProvider("My OleDb Server name", null, WrOleDbVendor.FactoryClass);
			WrVendor.OdbcDriverInfo[] providers = vendor.GetOdbcProviders();
			foreach (WrVendor.OdbcDriverInfo info in providers)
			{
				string name = info.Name.ToLower().Trim();
				if (name.StartsWith("sql") && (name.IndexOf("client") != -1))
				{
					OleDbProvider = info.Driver;
					break;
				}
			}
		}

		[SetUp]
		public void Init()
		{
			NameValueCollection appSettings = ConfigurationManager.AppSettings;
			serverName = appSettings["OleDbServer"];
			databaseName = appSettings["OleDbDatabase"];
			username = appSettings["OleDbUsername"];
			password = appSettings["OleDbPassword"];
		}

		[Test]
		public void TestCreateProvider()
		{
			WrOleDbVendor vendor = (WrOleDbVendor)WrProviderFactories.CreateProvider("My OleDb Server name", null, WrOleDbVendor.FactoryClass);
			Assert.IsNotNull(vendor);
			Assert.AreEqual(true, vendor.CanEnumerateDatabases);
			Assert.AreEqual(false, vendor.CanEnumerateServers);
			Assert.AreEqual(false, vendor.CanLaunchAdministrator);
			Assert.AreEqual(false, vendor.CanRunScripts);
			Assert.AreEqual(true, vendor.CanUseTrustedConnection);
			Assert.AreEqual(WrVendor.DATABASE_MODE.OPTIONAL, vendor.DatabaseMode);
			Assert.AreEqual(true, vendor.HasProviders);
			Assert.AreEqual("My OleDb Server name", vendor.Name);

			// enum providers
			WrVendor.OdbcDriverInfo[] providers = vendor.GetOdbcProviders();
			Assert.IsTrue(providers.Length > 5);

			vendor.OdbcProvider = OleDbProvider;
			Assert.AreEqual("Provider=" + OleDbProvider + ";Data Source=abc;Password=456;User ID=123;Initial Catalog=def",
				vendor.ConnectionString("abc", "def", new WrCredentials("123", "456"), true));
			Assert.AreEqual("Provider=" + OleDbProvider + ";Data Source=abc;Initial Catalog=def",
				vendor.ConnectionString("abc", "def", new WrCredentials(), true));
		}

		[Test]
		public void TestNorthwindEnumeration()
		{
			// enum databases, make sure has test one
			WrOleDbServer server = new WrOleDbServer(providerFactory, OleDbProvider, serverName);
			Assert.AreEqual(serverName, server.Name);
			IWrDatabase[] databases = server.GetDatabases(new WrCredentials(username, password));
			Assert.IsTrue(databases.Length > 10);

			WrOleDbDatabase db = new WrOleDbDatabase(providerFactory, OleDbProvider, serverName, databaseName);

			// Northwind tables
			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.USER, null);
			WrTable[] tables = db.Tables;
			Assert.AreEqual(13, tables.Length);
			Assert.AreEqual("Categories", tables[0].FullName);
			Assert.IsFalse(tables[0].IsSystem);
			Assert.AreEqual("Territories", tables[12].FullName);
			Assert.IsFalse(tables[12].IsSystem);

			// Northwind views
			WrView[] views = db.Views;
			Assert.AreEqual(16, views.Length);
			Assert.AreEqual("Alphabetical list of products", views[0].FullName);
			Assert.IsFalse(views[0].IsSystem);
			Assert.AreEqual("Summary of Sales by Year", views[15].FullName);
			Assert.IsFalse(views[15].IsSystem);

			// Northwind stored procedures
			WrStoredProcedure[] procs = db.StoredProcedures;
			Assert.AreEqual(7, procs.Length);
			Assert.AreEqual("CustOrderHist", procs[0].FullName);
			Assert.IsFalse(procs[0].IsSystem);
			Assert.AreEqual(2, procs[0].Parameters.Length);
			Assert.AreEqual("@CustomerID", procs[0].Parameters[1].Name);
			Assert.AreEqual(ParameterDirection.Input, procs[0].Parameters[1].Direction);
			Assert.AreEqual("Ten Most Expensive Products", procs[6].FullName);
			Assert.IsFalse(procs[6].IsSystem);

			// Northwind.Employees columns
			WrColumn[] cols = db.Tables[4].Columns;
			Assert.AreEqual(18, cols.Length);
			Assert.AreEqual("EmployeeID", cols[0].FullName);
			Assert.AreEqual(DbType.Int32, cols[0].DbType);
			Assert.AreEqual("PhotoPath", cols[17].FullName);
			Assert.AreEqual(DbType.String, cols[17].DbType);


			// KailuaTest - all types
			db = new WrOleDbDatabase(providerFactory, OleDbProvider, serverName, "KailuaTest");
			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.USER, null);
			cols = db.Tables[0].Columns;
			Assert.AreEqual(24, cols.Length);
			// bugbug - assert each name & DbType. DbType is the key test
			// bugbug - Numeric is not in AllTypes - no translation.
		}

		// note - cannot insert a timestamp
		private readonly string[] colNames = {"kBinary", "kBoolean", "kChar", "kDateTime", "kDecimal", "kFloat", "kImage", "kInt", 
				"kMoney","kNchar", "kNtext", "kNvarchar", "kReal", "kSmallDateTime", "kSmallInt", "kSmallMoney", "kText",
				"kTinyInt", "kUniqueIdentifier", "kVarBinary", "kVarChar"};
		private readonly object[] colValues = {new byte[10], true, "dave", DateTime.Now, new Decimal(1.23), 4.56, new byte [5], 789,
				12.34, "shirley", "winter", "lucky", 56.78, DateTime.Now, 90, 1.23, "zoe",
				5, new Guid(), new byte [22], "cody"};

		[Test]
		public void TestInsert()
		{

			using (WrConnection conn = new WrConnection("OleDbWrite"))
			{
				conn.Open();

				// empty it to start the same
				using (WrCommand cmd = conn.CreateWrCommand())
				{
					cmd.CommandText = "delete from AllTypes";
					cmd.ExecuteNonQuery();
				}

				// test get the PK back
				long[] pk = new long[2];
				using (WrCommand cmd = conn.CreateWrCommand())
				{
					cmd.CommandText = "insert into AllTypes (kNvarchar) values (?)";
					cmd.Set(0, "dave");
					pk[0] = cmd.ExecuteInsert("pk");
					Assert.IsTrue(pk[0] > 0);
				}

				using (WrCommand cmd = conn.CreateWrCommand())
				{
					cmd.CommandText = "insert into AllTypes (kNvarchar) values (?)";
					cmd.Set(0, "shirley");
					pk[1] = cmd.ExecuteInsert("pk");
					Assert.IsTrue(pk[1] > pk[0]);
				}

				// test insert each var type
				for (int ind = 0; ind < colNames.Length; ind++)
				{
					using (WrCommand cmd = conn.CreateWrCommand())
					{
						cmd.CommandText = "insert into AllTypes (" + colNames[ind] + ") values (?)";
						cmd.Set(0, colValues[ind]);
						cmd.ExecuteInsert("pk");
					}
				}

				// write all together
				using (WrCommand cmd = conn.CreateWrCommand())
				{
					StringBuilder command = new StringBuilder("insert into AllTypes (");
					for (int ind = 0; ind < colNames.Length; ind++)
						command.Append(colNames[ind] + ",");
					command.Remove(command.Length - 1, 1);
					command.Append(") values (");
					for (int ind = 0; ind < colNames.Length; ind++)
						command.Append("?,");
					command.Remove(command.Length - 1, 1);
					command.Append(")");
					cmd.CommandText = command.ToString();

					for (int ind = 0; ind < colNames.Length; ind++)
						cmd.Set(ind, colValues[ind]);

					pk[0] = cmd.ExecuteInsert("pk");
				}

				// test read each var type
				byte[] data = new byte[20];
				char[] chrs = new char[20];
				using (WrCommand cmd = conn.CreateWrCommand())
				{
					cmd.CommandText = "select * from AllTypes where pk = ?";
					cmd.Set(0, pk[0]);
					using (WrDataReader reader = cmd.WrExecuteReader())
					{
						Assert.IsTrue(reader.Read());
						Assert.AreEqual(pk[0], reader.GetInt64(0));
						Assert.AreEqual(4, reader.GetBytes(1, 0, data, 0, 4));
						Assert.AreEqual(colValues[1], reader.GetBoolean(2));
						Assert.AreEqual("dave      ", reader.GetString(3));
						Assert.AreEqual(((DateTime)colValues[3]).Date, reader.GetDateTime(4).Date);
						//						Assert.AreEqual(colValues[4], reader.GetDecimal(5));
						Assert.AreEqual(colValues[5], reader.GetValue(6));
						Assert.AreEqual(colValues[5], reader.GetDouble(6));
						Assert.AreEqual(4, reader.GetBytes(7, 0, data, 0, 4));
						Assert.AreEqual(colValues[7], reader.GetInt32(8));
						Assert.AreEqual(new decimal(12.3400), reader.GetDecimal(9));
						Assert.AreEqual(new decimal(12.3400), reader.GetDecimal(9));
						Assert.AreEqual("shirley   ", reader.GetString(10));
						Assert.AreEqual(colValues[10], reader.GetString(11));
						Assert.AreEqual(colValues[11], reader.GetString(12));
						Assert.AreEqual(colValues[12], reader.GetFloat(13));
						Assert.AreEqual(((DateTime)colValues[13]).Date, reader.GetDateTime(14).Date);
						Assert.AreEqual(colValues[14], reader.GetInt16(15));
						Assert.AreEqual(new decimal(1.2300), reader.GetDecimal(16));
						Assert.AreEqual(colValues[16], reader.GetString(17));
						Assert.IsTrue(reader.GetTimestamp(18) > 0);
						Assert.AreEqual(colValues[17], reader.GetByte(19));
						Assert.AreEqual(colValues[18], reader.GetGuid(20));
						Assert.AreEqual(4, reader.GetBytes(21, 0, data, 0, 4));
						Assert.AreEqual(colValues[20], reader.GetString(22));

						// remaining calls
						Assert.IsFalse(reader.IsDBNull(1));
						Assert.AreEqual("kBoolean", reader.GetName(2));
						Assert.AreEqual("DBTYPE_CHAR", reader.GetDataTypeName(3));
						Assert.AreEqual(typeof(DateTime), reader.GetFieldType(4));
						Assert.AreEqual(5, reader.GetOrdinal("kDecimal"));
						Assert.AreEqual(24, reader.FieldCount);
						Assert.AreEqual("lucky", reader[12]);
						Assert.AreEqual("lucky", reader["kNvarchar"]);

						object[] all = new object[24];
						Assert.AreEqual(24, reader.GetValues(all));
						Assert.AreEqual(pk[0], all[0]);
						Assert.AreEqual("lucky", all[12]);
					}
				}

				// test select top N
				using (WrCommand cmd = conn.CreateWrCommand())
				{
					cmd.CommandText = "  select   * from AllTypes order by pk DESC  top  1  ";
					Assert.AreEqual("select top  1 * from AllTypes order by pk DESC", cmd.CommandText);
					using (WrDataReader reader = cmd.WrExecuteReader())
					{
						Assert.IsTrue(reader.Read());
						Assert.AreEqual(pk[0], reader.GetInt64(0));
					}
				}
			}
		}
	}
}
